package ar.com.cubika.trainees.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;

import ar.com.cubika.trainees.bean.transportes.Chofer;
import ar.com.cubika.trainees.date.DateUtils;
import ar.com.cubika.trainees.date.RangoDeFechas;

public class RangoDeFechaDAO extends AbstractMySQLDAO {

	// Singleton
	private static RangoDeFechaDAO instance = new RangoDeFechaDAO();

	private RangoDeFechaDAO() {
	}

	public static RangoDeFechaDAO getInstance() {
		return instance;
	}

	public RangoDeFechas get(int id) {

		RangoDeFechas rango = null;
		try {

			Statement stmt = getNewStatement();

			ResultSet rs = stmt.executeQuery("SELECT "
					+ "FECHADESDE,FECHAHASTA FROM RANGOFECHAS WHERE ID=" + id
					+ ";");

			while (rs.next()) {
				rango = new RangoDeFechas(rs.getDate("FECHADESDE"),
						rs.getDate("FECHAHASTA"));
			}

			stmt.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return rango;
	}

	/**
	 * Crea un rango de fechas y retorna el ID del rango creado
	 * 
	 * @param unRango
	 * @return
	 */
	public synchronized int insertRango(RangoDeFechas unRango) {

		Date fechaDesde = unRango.getFechaInicio();
		Date fechaHasta = unRango.getFechaFin();
		int id = -1;

		try {

			Statement stmt1 = getNewStatement();

			String query = "INSERT INTO RANGOFECHAS (FECHADESDE,FECHAHASTA) VALUES ("
					+ this.convertDate(fechaDesde)
					+ ","
					+ this.convertDate(fechaHasta) + ");";

			stmt1.executeUpdate(query);

			stmt1.close();

			Statement stmt2 = getNewStatement();

			String maxID = "SELECT MAX(ID) AS 'MAX' FROM RANGOFECHAS";

			ResultSet rs = stmt2.executeQuery(maxID);

			while (rs.next()) {
				id = rs.getInt("MAX");
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return id;
	}

	public Chofer deleteRangoFecha(int id) {

		try {
			Statement stmt1 = getNewStatement();

			String query = "DELETE FROM RANGOFECHAS WHERE ID ='" + id + "';";

			stmt1.executeUpdate(query);

			stmt1.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return null;
	}

	private String convertDate(Date date) {

		// STR_TO_DATE('01/05/2010','%d/%m/%Y')
		StringBuilder result = new StringBuilder("STR_TO_DATE('");
		result.append(DateUtils.formatDate(date));
		result.append("','%d/%m/%Y')");
		return result.toString();
	}

}